package write;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;




public class MergeRecords {
	
	public static void mergeRecords(int idperson1, int idperson2)
	{
		String name1 = new String();
		String name2 = new String();
		String state1 = new String();
		String city1 = new String();
		String street1 = new String();
		String zip1 = new String();
		String phone1 = new String();
		String age1 = new String();
		String state2 = new String();
		String city2 = new String();
		String street2 = new String();
		String zip2 = new String();
		String phone2 = new String();
		String age2 = new String();
		//String url;	
		
		
		try
	   	{
	   	Statement stmt = getConn().createStatement();
			
	   	//extract records for first ID
	   	String query1 = "select * from person where idperson = " + idperson1 + ";";
			   //executing query and get the minimum id
			System.out.println(query1);
			   ResultSet rs1 = stmt.executeQuery(query1);
			   while(rs1.next()){
				   name1 = rs1.getString("name");
				   state1 = rs1.getString("state");
				   city1 = rs1.getString("city");
				   street1 = rs1.getString("street_address");
				   zip1 = rs1.getString("zipcode");
				   phone1 = rs1.getString("phone");
				   age1 = rs1.getString("age");
				  }
			 //extract records for first ID
			   	String query2 = "select * from person where idperson = " + idperson2 + ";";
					   //executing query and get the minimum id
					System.out.println(query2);
					   ResultSet rs2 = stmt.executeQuery(query2);
					   while(rs2.next()){
						   name2 = rs2.getString("name");
						   state2 = rs2.getString("state");
						   city2 = rs2.getString("city");
						   street2 = rs2.getString("street_address");
						   zip2 = rs2.getString("zipcode");
						   phone2 = rs2.getString("phone");
						   age2 = rs2.getString("age");
						  }
					      
			String newStreet =  mergeAttribute("street_address", street1, street2, idperson1, idperson2);			   
			String newZip =  mergeAttribute("zipcode", zip1, zip2, idperson1, idperson2);
			String newPhone =  mergeAttribute("phone", phone1, phone2, idperson1, idperson2);
			String newAge =  mergeAttribute("age", age1, age2, idperson1, idperson2);
			  
			
			//add condition that none of new attributes are ""
			  if((name1.equalsIgnoreCase(name2)) && (city1.equalsIgnoreCase(city2)) && (state1.equalsIgnoreCase(state2)) && (!newStreet.equals("")) && (!newZip.equals("")) && (!newPhone.equals("")) && (!newAge.equals("")))
			  {
				  int newID = getMaxID() + 1;
				  int newAssignedID = getMaxAssignedID() + 1;
				  String insert_query = "INSERT INTO person(idperson,name,state,city, street,zip,phone,age,assigned_id,url) values (?,?,?,?,?,?,?,?,?,?)";
			      PreparedStatement pstmt = getConn().prepareStatement(insert_query);    
				  pstmt.setInt(1, newID);
				  pstmt.setString(2, name1);
				  pstmt.setString(3, state1);
				  pstmt.setString(4, city1);
				  pstmt.setString(5, newStreet);
				  pstmt.setString(6, newZip);
				  pstmt.setString(7, newPhone);
				  pstmt.setString(8, newAge);
				  pstmt.setInt(9, newAssignedID);
				  pstmt.setString(10, "www.mergedrecord.com");
				  
				  Statement updateStmt1 = getConn().createStatement();
				  String updateQuery1 = "update person set assigned_id = -1 where idperson = " + idperson1 + ");";
				  stmt.executeUpdate(updateQuery1); 
				  
				  Statement updateStmt2 = getConn().createStatement();
				  String updateQuery2 = "update person set assigned_id = -1 where idperson = " + idperson2 + ");";
				  stmt.executeUpdate(updateQuery2);
			 
			  }
			  
			  else
			  {
				  int assignedId =0;
				  Statement selectStmt = getConn().createStatement();
				  String selectQuery = "select assigned_id from person where idperson = " + idperson1 + ";";
			      ResultSet rs = stmt.executeQuery(selectQuery);
				  
			      while(rs.next())
			      {
			    	assignedId =rs.getInt("assigned_id");  
			      }
				  
				  
				  Statement updateStmt = getConn().createStatement();
				  String updateQuery = "update person set assigned_id =" + assignedId +";";
				  stmt.executeUpdate(updateQuery);
				
			  }
			  
			  
			  
			   
			   
	   	}
	   	catch(SQLException se)
	   	{
	   		se.printStackTrace();
	   	}
		
		
		
		
		
	}
	
	public static String mergeAttribute(String attributeType, String attribute1, String attribute2, int id1, int id2)
	{
		String mergedAttribute = new String();
		
		if(attributeType.equals("age") && (!(attribute1.equals("")) && (!attribute1.equals(" "))) && (!(attribute2.equals("")) && (!attribute2.equals(" "))))
		{
		int age1 = Integer.parseInt(attribute1);
		int age2 = Integer.parseInt(attribute1);	
		
		if(age1>age2)
		{
			try{
				
				Statement stmt = getConn().createStatement();
				String query1 = "update person set age =" + age1 + "where idperson = " + id2 + ";";
				stmt.executeUpdate(query1);
				mergedAttribute = Integer.toString(age1);
				
				}
				
			 	catch(SQLException se)
			   	{
			   		se.printStackTrace();
			   	}
		}
			else if (age2>age1)
			{
				try{
					
					Statement stmt = getConn().createStatement();
					String query1 = "update person set age =" + age2 + "where idperson = " + id1 + ";";
					stmt.executeUpdate(query1);
					mergedAttribute = Integer.toString(age2);
					}
					
				 	catch(SQLException se)
				   	{
				   		se.printStackTrace();
				   	}
			}
		}
		
		
		
		else if((attribute1.equals("") || attribute1.equals(" ")) && (!(attribute2.equals("")) && (!attribute2.equals(" "))))
		{
			try{
			
			Statement stmt = getConn().createStatement();
			String query1 = "update person set" + attributeType + "=" + attribute2 + "where idperson = " + id1 + ";";
			stmt.executeUpdate(query1);
			mergedAttribute = attribute2;
			
			}
			
		 	catch(SQLException se)
		   	{
		   		se.printStackTrace();
		   	}
			
		}
		
		else if((attribute2.equals("") || attribute2.equals(" ")) && (!(attribute1.equals("")) && (!attribute1.equals(" "))))
		{
			try{
			
			Statement stmt = getConn().createStatement();
			String query1 = "update person set" + attributeType + "=" + attribute1 + "where idperson = " + id2 + ";";
			stmt.executeUpdate(query1);
			mergedAttribute = attribute1;
			
			}
			
		 	catch(SQLException se)
		   	{
		   		se.printStackTrace();
		   	}
			
		}
		
		
		//conn.close() ;
		return mergedAttribute;
		}
		
		
	 public static int getMaxID()
	    {
	    	int maxid = 0;
	    	try
	    	{
	    	Statement stmt = getConn().createStatement();
			String query = "select max(idperson) from person";
			   //executing query and get the maximum id
			System.out.println(query);
			   ResultSet rs1 = stmt.executeQuery(query);
			   if(rs1.next()){
	               maxid=rs1.getInt("max(idperson)");
	            }
	    	}
	    	catch(SQLException se)
	    	{
	    		se.printStackTrace();
	    	}
			   
			   return maxid;
	    }

	 public static int getMaxAssignedID()
	    {
	    	int maxAssignedId = 0;
	    	try
	    	{
	    	Statement stmt = getConn().createStatement();
			String query = "select max(assigned_id) from person";
			   //executing query and get the maximum id
			System.out.println(query);
			   ResultSet rs1 = stmt.executeQuery(query);
			   if(rs1.next()){
				   maxAssignedId=rs1.getInt("max(assigned_id)");
	            }
	    	}
	    	catch(SQLException se)
	    	{
	    		se.printStackTrace();
	    	}
			   
			   return maxAssignedId;
	    }
	    
	public static void main(String args[])
	{
		mergeRecords(1837,1840);
	}
	
	
	
	 static Connection conn = null;
	   static String userName = "root";
	   static String password = "admin";
	   static String url = "jdbc:mysql://localhost/aparna";
	   
	   public static Connection getConn()
	   {
	   	
	   	try
	   	{
	   	Class.forName ("com.mysql.jdbc.Driver").newInstance ();
	       conn = DriverManager.getConnection (url, userName, password);
	       System.out.println ("Database connection established");
	   	}
	   	
	   	catch(Exception e)
	   	{
	   		e.printStackTrace();
	   	}
	   	
	   	return conn;
	   	  	
	   	}
		
		
	}
	
	
	
